﻿#region Using Region

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data;
using ICE.Vacations.Helper;
using ICE.Vacations.Helper.Services;

#endregion

namespace ICE.Vacations.DataBase
{
    public class SQLDataClass
    {
        private SqlConnection sqlConnection { get; set; }

        public void OpenConnection()
        {
            try
            {
                Services.sendLog("Intentando conectarse al servidor: " + ConfigApp.SQL_Server, Services.errorType.InformationMessage);
                string h = "Server=LAB82\\INFOIMGR;Database=VACATIONS;User Id=sa;Password=$Unisys2k10;";

               // string h = "Server=" + ConfigApp.SQL_Server + ";Database=" + ConfigApp.SQL_DataBase + ";User Id=" + ConfigApp.SQL_User + ";Password=" + ConfigApp.SQL_Password + ";";
                Services.sendLog(h, Services.errorType.InformationMessage);

                sqlConnection = new SqlConnection(h);

                sqlConnection.Open();
                Services.sendLog("Conexion establecida con el servidor " + ConfigApp.SQL_Server, Services.errorType.InformationMessage);


            }
            catch (SqlException sqlExc)
            {
                throw (sqlExc);
            }
        }

        public string getConsecutivo()
        {
            try
            {
                this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "CONSECUTIVOVACACIONES";
                
                Services.sendLog("Ejecunto Store Procedure [" + sqlCommand.CommandText + "]...", Services.errorType.InformationMessage);
            // sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure 
                
                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure 
                Services.sendLog("Store Procedure ejecutado satisfactoriamente", Services.errorType.SuccessMessage);
               
                if (reader.HasRows)
               {
                   reader.Read();
                   Services.sendLog("Obteniendo resultado del store procedure...", Services.errorType.SuccessMessage);

                   string result =reader.GetString(0);
                   Services.sendLog("Resultado obtenido [" + result + "].", Services.errorType.InformationMessage);
                   this.CloseConnection();
                    
                   return result;
               }
               
                else {
                    Services.sendLog("Resultado obtenido [" + reader.HasRows + "].", Services.errorType.InformationMessage);

                    
                    return ""; }

              
            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        }

        public void CloseConnection()
        {
            try
            {
                Services.sendLog("Intentado cerrar la conexión con Servidor [" + ConfigApp.SQL_Server + "].", Services.errorType.InformationMessage);
                sqlConnection.Close();
                sqlConnection.Dispose();
                Services.sendLog("Conexión al servidor ["+ConfigApp.SQL_Server + "] cerrada satisfactoriamente.",Services.errorType.InformationMessage);
            }
            catch (SqlException sqlExc)
            {
                throw (sqlExc);
            }
        }

        public bool UPDATE_State(string requestID, string state)
        {

            try
            {
                this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "ACTUALIZAR_SOLICITUD";


                SqlParameter objID = sqlCommand.Parameters.Add("@IDSOLICITUD", SqlDbType.VarChar);
                objID.Direction = ParameterDirection.Input;
                objID.Value = requestID;


                SqlParameter HISTORICO = sqlCommand.Parameters.Add("@ESTADO_SOLICITUD", SqlDbType.VarChar);
                HISTORICO.Direction = ParameterDirection.Input;
                HISTORICO.Value = state;

                int result = sqlCommand.ExecuteNonQuery();
             
                    this.CloseConnection();

                return true;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        
        }
        public bool SQLUpdate(string objectID, string version, string historico, bool openConnection)
        {
            try
            {
                if (openConnection)
                    this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                //sqlCommand.CommandTimeout = Convert.ToInt32(Configuration.topQuery) * (60000);

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                //sqlCommand.CommandText = Configuration.updateCommand;


                SqlParameter objID = sqlCommand.Parameters.Add("@sOBJECT_ID", SqlDbType.VarChar);
                objID.Direction = ParameterDirection.Input;
                objID.Value = objectID;

                SqlParameter VERSION_WORKITEM = sqlCommand.Parameters.Add("@sVersion_Workitem", SqlDbType.VarChar);
                VERSION_WORKITEM.Direction = ParameterDirection.Input;
                VERSION_WORKITEM.Value = version;

                SqlParameter HISTORICO = sqlCommand.Parameters.Add("@sHistorico", SqlDbType.VarChar);
                HISTORICO.Direction = ParameterDirection.Input;
                HISTORICO.Value = historico;

                int result = sqlCommand.ExecuteNonQuery();
                if (openConnection)
                    this.CloseConnection();

                return true;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        }

        public bool SQLInsertSolicitud(string idSolicitud,string usuario,string empleado,int tDiasDisponibles,
            int tDiasSolicitados,string fechaInicio, string fechaFin, string fechaRegistro, string fechaWFL, string estado, string aprobador1, string aprobador2, string aprobador3,bool openConnection)
        {
            try
            {
                if (openConnection)
                    this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "INSERTAR_SOLICITUD";

                Services.sendLog("Insertando @sIDSOLICITUD: [" + idSolicitud + "]", Services.errorType.InformationMessage);
                SqlParameter objID = sqlCommand.Parameters.Add("@sIDSOLICITUD", SqlDbType.VarChar);
                objID.Direction = ParameterDirection.Input;
                objID.Value = idSolicitud;

                Services.sendLog("Insertando @sUSUARIO: [" + usuario + "]", Services.errorType.InformationMessage);
                SqlParameter USUARIO = sqlCommand.Parameters.Add("@sUSUARIO", SqlDbType.VarChar);
                USUARIO.Direction = ParameterDirection.Input;
                USUARIO.Value = usuario;

                Services.sendLog("Insertando @sEMPLEADO: [" + empleado + "]", Services.errorType.InformationMessage);
                SqlParameter EMPLEADO = sqlCommand.Parameters.Add("@sEMPLEADO", SqlDbType.VarChar);
                EMPLEADO.Direction = ParameterDirection.Input;
                EMPLEADO.Value = empleado;

                Services.sendLog(" Insertando @sTDIASDISPONIBLES: [" + tDiasDisponibles + "]", Services.errorType.InformationMessage);
                SqlParameter TDIASDISPONIBLES = sqlCommand.Parameters.Add("@sTDIASDISPONIBLES", SqlDbType.VarChar);
                TDIASDISPONIBLES.Direction = ParameterDirection.Input;
                TDIASDISPONIBLES.Value = tDiasDisponibles;

                Services.sendLog("Insertando @sTDIASSOLICITADOS: [" + tDiasSolicitados + "]", Services.errorType.InformationMessage);
                SqlParameter TDIASSOLICITADOS = sqlCommand.Parameters.Add("@sTDIASSOLICITADOS", SqlDbType.VarChar);
                TDIASSOLICITADOS.Direction = ParameterDirection.Input;
                TDIASSOLICITADOS.Value = tDiasSolicitados;

                Services.sendLog("Insertando @sFECHA_INICIO: [" + fechaInicio + "]", Services.errorType.InformationMessage);
                SqlParameter FECHA_INICIO = sqlCommand.Parameters.Add("@sFECHA_INICIO", SqlDbType.VarChar);
                FECHA_INICIO.Direction = ParameterDirection.Input;
                FECHA_INICIO.Value = fechaInicio;

                Services.sendLog("Insertando @sFECHA_FIN: [" + fechaFin + "]", Services.errorType.InformationMessage);
                SqlParameter FECHA_FIN = sqlCommand.Parameters.Add("@sFECHA_FIN", SqlDbType.VarChar);
                FECHA_FIN.Direction = ParameterDirection.Input;
                FECHA_FIN.Value = fechaFin;

                Services.sendLog("Insertando @sFECHA_REGISTRO: [" + DateTime.Now.ToString("dd/MM/yyyy") + "]", Services.errorType.InformationMessage);
                SqlParameter FECHA_REGISTRO = sqlCommand.Parameters.Add("@sFECHA_REGISTRO", SqlDbType.VarChar);
                FECHA_REGISTRO.Direction = ParameterDirection.Input;
                FECHA_REGISTRO.Value = DateTime.Now.ToString("dd/MM/yyyy");

                Services.sendLog("Insertando @sFECHA_ENVIO_WF: [" + DateTime.Now.ToString("dd/MM/yyyy") + "]", Services.errorType.InformationMessage);
                SqlParameter FECHA_ENVIO_WF = sqlCommand.Parameters.Add("@sFECHA_ENVIO_WF", SqlDbType.VarChar);
                FECHA_ENVIO_WF.Direction = ParameterDirection.Input;
                FECHA_ENVIO_WF.Value = fechaWFL;

                Services.sendLog("Insertando @sESTADO_SOLICITUD: [" + estado + "]", Services.errorType.InformationMessage);
                SqlParameter ESTADO_SOLICITUD = sqlCommand.Parameters.Add("@sESTADO_SOLICITUD", SqlDbType.VarChar);
                ESTADO_SOLICITUD.Direction = ParameterDirection.Input;
                ESTADO_SOLICITUD.Value = estado;

                Services.sendLog("Insertando @sAPROBADOR1: [" + aprobador1 + "]", Services.errorType.InformationMessage);
                SqlParameter APROBADOR1 = sqlCommand.Parameters.Add("@sAPROBADOR1", SqlDbType.VarChar);
                APROBADOR1.Direction = ParameterDirection.Input;
                APROBADOR1.Value = aprobador1;

                Services.sendLog("Insertando @sAPROBADOR2: [" + aprobador2 + "]", Services.errorType.InformationMessage);
                SqlParameter APROBADOR2 = sqlCommand.Parameters.Add("@sAPROBADOR2", SqlDbType.VarChar);
                APROBADOR2.Direction = ParameterDirection.Input;
                APROBADOR2.Value = aprobador2;

                Services.sendLog("Insertando @sAPROBADOR3: [" + aprobador3 + "]", Services.errorType.InformationMessage);
                SqlParameter APROBADOR3 = sqlCommand.Parameters.Add("@sAPROBADOR3", SqlDbType.VarChar);
                APROBADOR3.Direction = ParameterDirection.Input;
                APROBADOR3.Value = aprobador3;
              sqlCommand.ExecuteNonQuery();

                if (openConnection)
                    this.CloseConnection();

                return true;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        }

        public int TotalAprovees(string employeeID)
        {
            try
            {
                // this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "TOTAL_APROBADORES";


                SqlParameter parameter = sqlCommand.Parameters.Add("@sCEDULA", SqlDbType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = employeeID;

               SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure   
               if (reader.HasRows)
               {
                   reader.Read();
                   return Convert.ToInt32(reader.GetString(0));
               }
               else { return 0; }

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
               
                return 0;
            }
        }

        public void UpdateHistorico(string requestID, string fecha, string tipoTrans, string employeeID, bool notified, string state, string comment)
        {
            throw new System.NotImplementedException();
        }

        public SqlDataReader SearchByBoss(string bossID)
        {
            try
            {
                 this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "SEARCHBYBOSS";


                SqlParameter parameter = sqlCommand.Parameters.Add("@BOSSID", SqlDbType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = bossID;

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure   
                return reader;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                Services.sendLog("Ocurrio un error de base de datos " + ex.Message , Services.errorType.DBError);
                return null;
            }
            //throw new System.NotImplementedException();
        }

        public bool CommentRequest(string requestID, string comment, bool openConnection)
        {

            try
            {
                if (openConnection)
                    this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                //sqlCommand.CommandTimeout = Convert.ToInt32(Configuration.topQuery) * (60000);

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                //sqlCommand.CommandText = Configuration.updateCommand;


                SqlParameter IDSOLICITUD = sqlCommand.Parameters.Add("@IDSOLICITUD", SqlDbType.VarChar);
                IDSOLICITUD.Direction = ParameterDirection.Input;
                IDSOLICITUD.Value = requestID;

                SqlParameter COMMENT = sqlCommand.Parameters.Add("@COMMENT", SqlDbType.VarChar);
                COMMENT.Direction = ParameterDirection.Input;
                COMMENT.Value = comment;

               

                int result = sqlCommand.ExecuteNonQuery();
                if (openConnection)
                    this.CloseConnection();

                return true;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }

            
        }

        public SqlDataReader SearchEmployee(string username)
        {
            try
            {
                this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "SELECT_RECURSO";
                Services.sendLog("Ejecutando Store Procedure [" + sqlCommand.CommandText + "]...", Services.errorType.InformationMessage);

                SqlParameter parameter = sqlCommand.Parameters.Add("@sUSUARIO", SqlDbType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = username;
                Services.sendLog("Valor del parametro [" + parameter.Value + "]...", Services.errorType.InformationMessage);

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure   
                
                return reader;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                Services.sendLog("Ocurrio un error de base de datos " + ex.Message, Services.errorType.DBError);
                return null;
            }
        }

        public string getTipoUsuario(string username)
        {
            try
            {
                string result;
                this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "SEARCH_TIPO";
                Services.sendLog("Ejecutando Store Procedure [" + sqlCommand.CommandText + "]...", Services.errorType.InformationMessage);

                SqlParameter parameter = sqlCommand.Parameters.Add("@USUARIO", SqlDbType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = username;
                Services.sendLog("Valor del parametro [" + parameter.Value + "]...", Services.errorType.InformationMessage);

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure   
                if (reader.HasRows)
                {
                    reader.Read();
                    result = reader.GetSqlValue(reader.GetOrdinal("TIPO")).ToString();
                }
                else {
                    result = "";
                }

                return result;
            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                Services.sendLog("Ocurrió un error de base de datos " + ex.Message, Services.errorType.DBError);
                return null;
            }
        }

        public SqlDataReader Buscar_Recurso(string idusuario)
        {
            try
            {
                this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "BUSCAR_RECURSO";
                Services.sendLog("Ejecutando Store Procedure [" + sqlCommand.CommandText + "]...", Services.errorType.InformationMessage);

                SqlParameter parameter = sqlCommand.Parameters.Add("@IDUSUARIO", SqlDbType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = idusuario;
                Services.sendLog("Valor del parametro [" + parameter.Value + "]...", Services.errorType.InformationMessage);

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure   

                return reader;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                Services.sendLog("Ocurrio un error de base de datos " + ex.Message, Services.errorType.DBError);
                return null;
            }
        }
    }
}
